﻿using System;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Xml;
using System.IO;

using Microsoft.AnalysisServices.AdomdClient;

namespace KoOLAP_TestWinForms
{

    public class BI_SQL_CashAtYear
    {

        #region Common

        public static string MakeDT_Y_M(int Year, int Month)
        {
            return string.Format("{0:0000}{1:00}", Year, Month);
        }

        #endregion

        public Exception Exec(ref CellSet parCst, string parConnStr, string parCmdStr)
        {
            Exception ret = null;
            try
            {
                using (AdomdConnection conn = new AdomdConnection(parConnStr))
                {
                    conn.Open();
                    using (AdomdCommand cmd = new AdomdCommand(parCmdStr, conn))
                    {
                        parCst = cmd.ExecuteCellSet();
                        conn.Close();
                    }
                }
            }
            catch (Exception err)
            {
                parCst = null;
                ret = new Exception(err.Message, err);
            }

            return ret;
        }

        public Exception Get(ref DataTable tbl, string parConnStr, int parYear, int parMonth)
        {
            #region Members

            Exception ret = null;
            string MonthStr = MakeDT_Y_M(parYear, parMonth);

            #region SQL
            /*
SELECT NON EMPTY 
Hierarchize({DrilldownLevel({[Дата].[Год].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME 
ON COLUMNS , 
NON EMPTY Hierarchize({DrilldownLevel({[Дата].[Месяц].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Дата].[Месяц].[Месяц].[Номер месяца] 
ON ROWS 
FROM [KPI] 
WHERE ([Measures].[Оборот7 Руб]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

            */
            string cmdStr =
@"
SELECT NON EMPTY 
Hierarchize({DrilldownLevel({[Дата].[Год].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME 
ON COLUMNS , 
NON EMPTY Hierarchize({DrilldownLevel({[Дата].[Месяц].[Все]},,,INCLUDE_CALC_MEMBERS)}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
,[Дата].[Месяц].[Месяц].[Номер месяца] 
ON ROWS 
FROM [KPI] 
WHERE ([Measures].[Оборот7 Руб]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
";

            #endregion SQL

            #endregion Members

            CellSet cst = null;
            Exception err = Exec(ref cst, parConnStr, cmdStr);

            if (err != null)
                ret = new Exception(err.Message);
            else
            {
                err = Parse_001(ref cst, ref tbl);
                if (err != null)
                    ret = new Exception(err.Message);
            }

            return ret;
        }

        public Exception Parse_001(ref CellSet parCst, ref DataTable parTbl)
        {
            Exception ret = null;

            #region Init

            // check if any axes were returned else throw error.
            int axes_count = parCst.Axes.Count;
            if (axes_count == 0)
                return new Exception("No data returned for the selection");

            // if axes count is not 2
            if (axes_count != 2)
                return new Exception("The sample code support only queries with two axes");

            // if no position on either row or column throw error
            if (!(parCst.Axes[0].Positions.Count > 0) && !(parCst.Axes[1].Positions.Count > 0))
                return new Exception("No data returned for the selection");

            #endregion

            #region Members

            int cur_row, cur_col, col_count, row_count, col_dim_count, row_dim_count;
            cur_row = cur_col = col_count = row_count = col_dim_count = row_dim_count = 0;

            // Number of dimensions on the column
            col_dim_count = parCst.Axes[0].Positions[0].Members.Count;

            // Number of dimensions on the row
            if (parCst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

            // Total rows and columns
            row_count = parCst.Axes[1].Positions.Count + col_dim_count;  //number of rows + rows for column headers
            col_count = parCst.Axes[0].Positions.Count + row_dim_count;  //number of columns + columns for row headers

            MakeTbl_001(ref parCst, ref parTbl, col_count);

            int count = 0;
            DataRow r = null;

            #endregion

            #region for

            for (cur_row = 0; cur_row < row_count - 1; cur_row++)
            {

                count++;
                if (count >= 1)
                    r = parTbl.NewRow();
                else
                    continue;

                #region Data

                for(int i = 0; i<col_count-1; i++)
                {
                    if(parCst[i, cur_row].Value != null)
                        r[i+1] = Convert.ToDecimal(parCst[i, cur_row].Value);
                    else
                        r[i+1] = DBNull.Value;
                }

                r[0] = parCst.Axes[1].Positions[cur_row].Members[0].Caption;

                parTbl.Rows.Add(r);

                #endregion

            }

            parTbl.Columns.Remove("Все");

            #endregion

            return ret;
        }

        public void MakeTbl_001(ref CellSet parCst, ref DataTable parTbl, int cnt)
        {
            int row_dim_count = 0, col_count;

            if (parTbl == null)
                parTbl = new DataTable();

            parTbl.Columns.Add("Месяц", typeof(string));
            parTbl.Columns.Add("Все", typeof(string));

            if (parCst.Axes[1].Positions[0].Members.Count > 0)
                row_dim_count = parCst.Axes[1].Positions[0].Members.Count;

            col_count = parCst.Axes[0].Positions.Count + row_dim_count;

            for (int i = 1; i < col_count - 1; i++)
            {
                string ColNameStr = parCst.Axes[0].Positions[i].Members[0].Caption;
                parTbl.Columns.Add(ColNameStr, typeof(Decimal));
            }

        }

    }

}
